//
//  DBManager.bundle.swift
//  ChinaPoem-Swift
//
//  Created by jh on 2022/11/29.
//

import SQLite
import Foundation

struct SQLiteQuery {
    let db: Connection
    init(dbPath: String) {
        let dataBase = try! Connection(dbPath)
        dataBase.busyTimeout = 5
        self.db = dataBase
    }
    
    func search<T: SQLProtocol>(page: Int = 0, limit: Int = 10, asc: Bool = true, option: @escaping () -> SQLite.Expression<Bool>) -> [T] {
        if asc {
            let table = T.table.where(option()).limit(limit, offset: page * limit)
            return done(table: table)
        } else {
            let table = T.table.where(option()).order(SQLite.Expression<Int>("id").desc).limit(limit, offset: page * limit)
            return done(table: table)
        }
    }
    
    func search<T: SQLProtocol>(key: String, value: String, page: Int = 0, limit: Int = 10, asc: Bool = true) -> [T] {
        if asc {
            let table = T.table.where(SQLite.Expression<String>(key) == value).limit(limit, offset: page * limit)
            return done(table: table)
        } else {
            let table = T.table.where(SQLite.Expression<String>(key) == value).order(SQLite.Expression<Int>("id").desc).limit(limit, offset: page * limit)
            return done(table: table)
        }
    }
    
    func searchKeys<T: SQLProtocol>(key1: String, value1: Int, key2: String, value2: String, page: Int = 0, limit: Int = 10, asc: Bool = true) -> [T] {
        if asc {
            let table = T.table.where(SQLite.Expression<Int>(key1) == value1 && SQLite.Expression<String>(key2) == value2).limit(limit, offset: page * limit)
            return done(table: table)
        } else {
            let table = T.table.where(SQLite.Expression<Int>(key1) == value1 && SQLite.Expression<String>(key2) == value2).order(SQLite.Expression<Int>("id").desc).limit(limit, offset: page * limit)
            return done(table: table)
        }
    }

    func searchInt<T: SQLProtocol>(key: String, value: Int, page: Int = 0, limit: Int = 10, asc: Bool = true) -> [T] {
        if asc {
            let table = T.table.where(SQLite.Expression<Int>(key) == value).limit(limit, offset: page * limit)
            return done(table: table)
        } else {
            let table = T.table.where(SQLite.Expression<Int>(key) == value).order(SQLite.Expression<Int>("id").desc).limit(limit, offset: page * limit)
            return done(table: table)
        }
    }
    
    func querys<T: SQLProtocol>(page: Int = 0, size: Int = 10, asc: Bool = true) -> [T] {
        if asc {
            let table = T.table.limit(size, offset: page * size)
            return done(table: table)
        } else {
            let table = T.table.order(SQLite.Expression<String>("id").desc).order(SQLite.Expression<Int>("id").desc).limit(size, offset: page * size)
            return done(table: table)
        }
    }
    
    func queryIn<T: SQLProtocol>(key: String, values: [Int], page: Int = 0, size: Int = 10, asc: Bool = true) -> [T] {
        if asc {
            let key = SQLite.Expression<Int>(key)
            let table = T.table.filter(values.contains(key)).limit(size, offset: page * size)
            return done(table: table)
        } else {
            let key = SQLite.Expression<Int>(key)
            let table = T.table.filter(values.contains(key)).order(SQLite.Expression<Int>("id").desc).limit(size, offset: page * size)
            return done(table: table)
        }
    }
    
    func randomDatas<T: SQLProtocol>(size: Int = 10) -> [T] {
        let query = T.table.order(SQLite.Expression<Int>.random()).limit(size)
        return done(table: query)
    }
    
    func randomsStr<T: SQLProtocol>(key: String, value: String, limit: Int = 10) -> [T] {
        let query = T.table.filter(SQLite.Expression<String>(key) == value).order(SQLite.Expression<Int>.random()).limit(limit)
        return done(table: query)
    }
    func randomsInt<T: SQLProtocol>(key: String, value: Int, limit: Int = 10) -> [T] {
        let query = T.table.filter(SQLite.Expression<Int>(key) == value).order(SQLite.Expression<Int>.random()).limit(limit)
        return done(table: query)
    }
        
    func count(table: String, key: String, value: String) -> Int {
        do {
            return try self.db.scalar(Table(table).where(SQLite.Expression<String>(key) == value).count)
        } catch {
            print (error)
        }
        return 0
    }
    
    func count(_ table: Table) -> Int {
        do {
            return try self.db.scalar(table.count)
        } catch {
            print (error)
        }
        return 0
    }
    
    
    func isExists<T: SQLProtocol>(value: String, key: String) -> [T] {
        let table = T.table.where(SQLite.Expression<String>(key) == value)
        return done(table: table)
    }
}

// MARK: -- 增、删、改、查
extension SQLiteQuery {
    func group<T: SQLProtocol>(key: String, value: Int, limit: Int = 10) -> [T] {
        return self.prepare(query: "select *, count(*) as count from tagAuthor group by type having poetId > 0 order by count desc;")
    }
}

// MARK: -- 增、删、改、查
extension SQLiteQuery {
    func insert<T: SQLProtocol>(model: T) {
        do {
            try self.db.run(T.table.insert(model))
        } catch {
            print (error)
        }
    }
    
    func delete<T: SQLProtocol>(model: T, key: String, value: Int) {
        do {
            let key = SQLite.Expression<Int>(key)
            let alice = T.table.filter(key == value)
            try self.db.run(alice.delete())
        } catch {
            print (error)
        }
    }
}

// MARK: -- 序列化
extension SQLiteQuery {
    // 模型查询
    func done<T: SQLProtocol>(table: Table) -> [T] {
        do {
            return try self.db.prepare(table).map({ try $0.decode() })
        } catch let jsonError {
            print (jsonError, T.table)
        }
        return []
    }
    
    // 使用SQL语句查询
    func prepare<T: SQLProtocol>(query: String) -> [T] {
        do {
            let statement = try self.db.prepare(query)
            var rows = [[String: Any]]()
            for row in statement {
                var info = [String: Any]()
                for (index, name) in statement.columnNames.enumerated() {
                    info[name] = row[index]!
                }
                rows.append(info)
            }
            return rows.compactMap({ T.deserialize(dic: $0) })
        } catch {
            print (error)
        }
        return []
    }
}
